clear all
set more off 

********************************************************************************
// Final descriptives
********************************************************************************

use "PCSALevelData_Raw.dta", clear
gen le_highschool = og_less_highschool + og_highschool
gen gte_highschool = og_highschool + og_some_college + og_bachelor
gen female = og_female 
gen age_65 = og_age_65 
gen uninsured = og_uninsured
ren hisp hispanic 
ren black nonhisp_black 
ren other nonhisp_other
ren white nonhisp_white
replace tot_pop = tot_pop*10000*1000
local vars "hispanic nonhisp_white nonhisp_black nonhisp_other female age_65 uninsured le_highschool gte_highschool"
foreach v of local vars{
     replace `v' = `v'/tot_pop
}
replace tot_pop = tot_pop/10000/1000
drop if income_pc==.

gen cat_ucc = n_urgentcare
replace cat_ucc = 3 if cat_ucc>3
gen cat_hosp = n_hospitals
replace cat_hosp = 1 if cat_hosp>1
gen cat_hosp2 = n_hospitals
replace cat_hosp2 = 0 if cat_hosp2<=1
replace cat_hosp2 = 1 if cat_hosp2>=2
gen cat_aucc = n_hospaffucc_geo
replace cat_aucc = 1 if cat_aucc>1
gen n_ucc_aucc = n_urgentcare + n_hospaffucc_geo
gen cat_both = n_ucc_aucc
replace cat_both = 4 if cat_both>4
gen tot_pop2 = tot_pop
gen tot_pop3 = tot_pop
gen any_hosp = (n_hospitals>0)
gen any_aucc = (n_hospaffucc_geo>0)
gen any_ucc = (n_urgentcare>0) 

gen median_income_pc = .
qui sum income_pc, detail
replace median_income_pc = r(p50)
gen high_income = (income_pc>=median_income_pc)
drop median_income_pc

gen median_svi = .
qui sum rpl_themes, detail
replace median_svi = r(p50)
gen high_svi = (rpl_themes>=median_svi)
drop median_svi

gen median_uninsured = .
qui sum uninsured, detail
replace median_uninsured = r(p50)
gen high_uninsured = (uninsured>=median_uninsured)
drop median_uninsured

ren n_hospitals og_n_hospitals
gen n_hospitals = og_n_hospitals
replace n_hospitals = cat_hosp2

ren n_hospaffucc_geo og_n_hospaffucc_geo
gen n_hospaffucc_geo = og_n_hospaffucc_geo
replace n_hospaffucc_geo = cat_aucc

ren n_urgentcare og_n_urgentcare 
gen n_urgentcare = og_n_urgentcare
replace n_urgentcare = cat_ucc

replace cat_aucc = og_n_hospaffucc_geo
replace cat_aucc = 2 if cat_aucc>2
replace n_hospaffucc_geo = cat_aucc

// Table 1
tabstat tot_pop rural income_pc hispanic nonhisp_black gte_highschool age_65 uninsured cms_wage_index any_hosp any_aucc, by(cat_ucc) stat(mean sd) columns(stat)

// Appendix Table 3
tabstat tot_pop rural income_pc hispanic nonhisp_black gte_highschool age_65 uninsured cms_wage_index any_hosp any_ucc, by(cat_aucc) stat(mean sd) columns(stat)

use "ZCTALevelData_R2.dta", clear
ren state statea 
ren county countya
tostring statea, gen(stated)
tostring countya, gen(countyd)
gen z = "0"
gen c_l = length(countyd)
gen s_l = length(stated)
gen state = ""
replace state = stated if s_l==2
egen state_input = concat(z stated), p("")
replace state = state_input if s_l==1
gen county2 = ""
replace county2 = countyd if c_l==3
egen county_input1 = concat(z countyd), p("")
egen county_input2 = concat(z z countyd), p("")
replace county2 = county_input1 if c_l==2
replace county2 = county_input2 if c_l==1
egen county = concat(state county2), p("")
drop county2 county_input* state_inpu c_l s_l z countyd stated
drop if state=="02"|state=="15"|state=="72"|state=="78"|state=="60"|state=="66"|state=="69"
drop if tot_pop==.
drop if tot_pop==0
drop if cms_wage_index==.
save "Descriptives.dta", replace

* UCC sector growth

use "Descriptives.dta", clear
collapse (sum) n_urgentcare n_hospaffucc_geo, by(year)
sort year
tempfile master
save `master'

* Hospital sector growth 

use "HospitalsGeocoded.dta", clear
gen establishments = 1
ren State state
collapse (sum) establishments, by(year)
drop if year>2017
merge 1:1 year using `master'
drop if _merge==2
drop _merge
gen ucc_hucc = n_urgentcare+n_hospaffucc_geo

// Figure 1
twoway line n_urgentcare year, lcolor(black) mcolor(black) lwidth(medthick) || line establishments year, lcolor(black) lpattern(dash) lwidth(medthick) ytitle("Number of establishments") graphregion(color(white))  xlabel(2005(1)2017, labsize(small)) legend(label(1 "UCC") label(2 "Hospital") rows(1) size(small) pos(6))  xtitle("Year") yscale(range(2000(1500)11000)) ylabel(2000(1500)11000, labsize(small)) xlabel(, angle(90)) aspectratio(1) xsize(5) ysize(5)
graph save "Establishment_UCC_National.gph", replace
graph export "Establishment_UCC_National.pdf", replace

* UCC location choices

use "ZCTALevelProcessed_R2_input.dta", clear
keep tot_pop state 
replace tot_pop = tot_pop*1000
gcollapse (sum) tot_pop, by(state)
merge 1:1 state using "state_abbr.dta"
keep if _merge==3
drop _merge
drop state 
ren state_name state
tempfile states
save `states'

use "Descriptives.dta", clear
collapse (sum) n_urgentcare, by(state year)
keep if year==2015
merge 1:1 state using "state_abbr.dta"
keep if _merge==3
drop _merge
drop state 
ren state_name state
merge 1:1 state using `states'
keep if _merge==3
drop _merge
replace n_urgentcare = n_urgentcare/tot_pop
ren state STATE_ABBR
tempfile establishments
save `establishments'

use "us_shape_files/usdb.dta", clear
merge 1:1 STATE_ABBR using `establishments', keep(3) nogen
save "us_shape_files/UCC_usdb.dta", replace

use "us_shape_files/UCC_usdb.dta", clear
drop if STATE_ABBR=="AK"
drop if STATE_ABBR=="HI"
gen labtype = 1
append using "us_shape_files/UCC_usdb.dta", force
drop if STATE_ABBR=="AK"
drop if STATE_ABBR=="HI"
replace labtype = 2 if labtype==.
replace y_centroid = y_centroid - 0.7
replace x_centroid = x_centroid + 0.5 if STATE_NAME=="Michigan"
replace y_centroid = y_centroid - 0.1 if STATE_NAME=="Michigan"
replace x_centroid = x_centroid - 0.3 if STATE_NAME=="Louisiana"
replace x_centroid = x_centroid + 0.7 if STATE_NAME=="Florida"
replace x_centroid = x_centroid + 2.5 if STATE_NAME=="Maryland"
replace y_centroid = y_centroid - 1 if STATE_NAME=="Maryland"
replace x_centroid = x_centroid + 2 if STATE_NAME=="Delaware"
replace x_centroid = x_centroid + 2 if STATE_NAME=="New Jersey"
replace y_centroid = y_centroid - 0.3 if STATE_NAME=="New Jersey"
replace x_centroid = x_centroid + 2 if STATE_NAME=="Connecticut"
replace y_centroid = y_centroid - 1 if STATE_NAME=="Connecticut"
replace x_centroid = x_centroid + 2.5 if STATE_NAME=="Rhode Island"
replace y_centroid = y_centroid - 0.2  if STATE_NAME=="Rhode Island"
replace x_centroid = x_centroid + 2.7 if STATE_NAME=="Massachusetts"
replace y_centroid = y_centroid + 0.4 if STATE_NAME=="Massachusetts"
replace y_centroid = y_centroid + 2.7 if STATE_NAME=="Vermont"
replace x_centroid = x_centroid - 0.5 if STATE_NAME=="Vermont"
replace y_centroid = y_centroid + 2.3 if STATE_NAME=="New Hampshire"
replace x_centroid = x_centroid + 0.2 if STATE_NAME=="New Hampshire"
replace STATE_ABBR = string(n_urgentcare, "%4.2f") if labtype==2
keep if labtype==2
keep STATE_ABBR x_centroid y_centroid labtype
replace STATE_ABBR = "{bf:" + STATE_ABBR + "}"
save "us_shape_files/UCC_usdb_labels.dta", replace

clear
input byte _ID str2 STUSPS float(_X _Y)
25 "CT"    .    .
25 "CT" -72.7 41.6
25 "CT" -71.7 40.6
26 "DE"    .    .
26 "DE" -75.5 38.9
26 "DE" -74.4 38.9
31 "MA"    .    .
31 "MA" -71.8 42.3
31 "MA" -70.1 42.7
10 "MD"    .    .
10 "MD" -76.8 39.1
10 "MD" -76.8 38.1
36 "NH"    .    .
36 "NH" -71.6 43.7
36 "NH" -71.6 45.7
37 "NJ"    .    .
37 "NJ" -74.67 40.19
37 "NJ" -73.3 40.0
41 "RI"    .    .
41 "RI" -71.6 41.7
41 "RI" -69.9 41.3
48 "VT"    .    .
48 "VT" -72.7 44.1
48 "VT" -72.7 46.3
end
drop STUSPS
save "us_shape_files/connectors.dta", replace

//Figure 2
use "us_shape_files/UCC_usdb.dta", clear
drop if STATE_ABBR=="AK"
drop if STATE_ABBR=="HI"
spmap n_urgentcare using "us_shape_files/uscoord.dta", id(id) fcolor(white gs14 gs10 gs7) label(data("us_shape_files/UCC_usdb_labels.dta") xcoord(x_centroid)  ycoord(y_centroid) label(STATE_ABBR) by(labtype) size(*1.25 ..) pos(12 0)) legend(size(medium)) clmethod(custom) clbreaks(0.10 0.20 0.30 0.40 0.60) line( data("us_shape_files/connectors.dta") size(medthick))
graph save "HeatmapStateEstablishments.gph", replace
graph export "HeatmapStateEstablishments.pdf", replace
